import logging
log = logging.getLogger(__name__)


from portal.lib.auth import private_page
import json
from pylons import request, response, session, tmpl_context as c, url
from pylons.controllers.util import abort, redirect

from portal.lib.base import BaseController, render
from portal import model
from portal.model.meta import Session, Base
from portal.model import ChatMessagesToUser



class ChatController(BaseController):

    def index(self):
        # Return a rendered template
        c.messages = ChatMessagesToUser.get_messages_from_the_day()
        return private_page('/chat/index.mako')
    
    
    def scan_for_new_messages(self):
        
        GET_MESSAGE = """
            SELECT cm.when_created as when_created, cm.id as mess_id, (SELECT last_name FROM user WHERE id = cm.us_id) as last_name  , cm.message as message 
            FROM  chat_message as cm
            WHERE cm.id NOT IN (SELECT cmtu.id_mess FROM chat_message_to_user as cmtu) 
            AND cm.us_id <> :us_id
            AND cm.when_created >= :time
         """
         
        INSERT_MESSAGE_TO_USER = """
            INSERT INTO chat_message_to_user 
            VALUES (NULL, :us_id, :id_mess)
            """
        
        messages = model.Session.execute(GET_MESSAGE, {'us_id' : session['user'], 'time' : session['chat_time']} ) 
        
        ids = []
        amessages = {}
        x = []
        for message in messages:
            ids.append(message.mess_id)
            
            x.append(message.last_name)
            x.append(message.message)
            x.append(str(message.when_created))
            amessages[message.mess_id]=x
            x = []
           
        for id in ids:
            model.Session.execute(INSERT_MESSAGE_TO_USER, {'us_id' : session['user'], 'id_mess' : id} )
        
        return json.dumps(amessages)
    
    
    def add_new_messages(self):
        message = request.params.get('message')
        us_id = request.params.get('us_id')
        INSERT_MESSAGE = """
            INSERT INTO chat_message 
            VALUES (NULL, :us_id, NOW(), :message);
            """
        
        add_message = model.Session.execute(INSERT_MESSAGE, {'us_id' : us_id, 'message' : message} ) 
        
        if add_message:
            return '1'
        else:
            return '0'
    
    
    def leaving_chat(self):
        us_id = request.params.get('us_id')
        DELETE_CHAT_USER = """
            DELETE FROM chat_who_is_connected
            WHERE us_id = :us_id;
            """
        
        remove_u = model.Session.execute(DELETE_CHAT_USER, {'us_id' : us_id} )
  
        
    def entering_chat(self):
        us_id = request.params.get('us_id')
        
        ALREADY_CONNECTED_USER = """
            SELECT us_id FROM chat_who_is_connected;
            """
        user_ids = model.Session.execute(ALREADY_CONNECTED_USER )
        
        already_connected_user = []
        
       
        
        for id in user_ids:
            already_connected_user.append(int(id.us_id))
        
        log.debug(already_connected_user)   
          
        INSERT_CHAT_USER = """
            INSERT INTO chat_who_is_connected
            VALUES (NULL, :us_id, 1);
            """
            
        if int(us_id) not in already_connected_user:
            add_u = model.Session.execute(INSERT_CHAT_USER, {'us_id' : us_id} )
            if add_u:
                return '1'
        
        return '0'
    
    
    def check_who_is_online(self):
        CHECK_CHAT_USER = """
            SELECT u.last_name as last_name FROM chat_who_is_connected as cwic, user as u
            WHERE u.id = cwic.us_id
            AND  cwic.connected = 1;
            """
        user_online = model.Session.execute(CHECK_CHAT_USER, {} )
        
        username = []
        
        for user in user_online:
            username.append(str(user.last_name))
        
        return json.dumps(username)